﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using CommonLibrary.DB;
using CommonLibrary.DataModel.Basic;
using System.Data;

namespace ServiceApplication.Dao.Basic
{
    public class TxlGroupDao
    {
        public int AddEntity(TxlGroupEntity txlGroupEntity)
        {
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@txlgroup_id", txlGroupEntity.txlgroup_id));
            parameterList.Add(new SqlParameter("@txlgroup_name", txlGroupEntity.txlgroup_name));
            parameterList.Add(new SqlParameter("@txlgroup_descn", txlGroupEntity.txlgroup_descn));
            parameterList.Add(new SqlParameter("@user_id", txlGroupEntity.user_id));


            string sql = "insert into txl_group (txlgroup_name,txlgroup_descn,user_id) values " +
                " (@txlgroup_name,@txlgroup_descn,@user_id); " +
                " select @@identity";
            string id = DBExecuteUtil.getUniqueResult(sql, parameterList.ToArray());
            return int.Parse(id);
        }

        public void DleteEntity(int txlgroup_id)
        {
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@txlgroup_id", txlgroup_id));
            string sql = "delete from txl_group where txlgroup_id=@txlgroup_id ";
            DBExecuteUtil.executeSqlWithPar(sql, parameterList.ToArray());
        }

        public void UpdateEntity(TxlGroupEntity txlGroupEntity)
        {
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@txlgroup_id", txlGroupEntity.txlgroup_id));
            parameterList.Add(new SqlParameter("@txlgroup_name", txlGroupEntity.txlgroup_name));
            parameterList.Add(new SqlParameter("@txlgroup_descn", txlGroupEntity.txlgroup_descn));
            parameterList.Add(new SqlParameter("@user_id", txlGroupEntity.user_id));

            string sql = " update txl_group set txlgroup_name=@txlgroup_name,txlgroup_descn=@txlgroup_descn,user_id=@user_id where txlgroup_id=@txlgroup_id ";

            DBExecuteUtil.executeSqlWithPar(sql, parameterList.ToArray());
        }

        public TxlGroupEntity QueryEntity(int txlgroup_id)
        {
            TxlGroupEntity txlGroupEntity = null;
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@txlgroup_id", txlgroup_id));
            string sql = "select txlgroup_id,txlgroup_name,txlgroup_descn,user_id " +
                " from txl_group where txlgroup_id=@txlgroup_id";

            var result = DBExecuteUtil.querySqlArray(sql, parameterList.ToArray());
            if (result == null) return null;

            txlGroupEntity = new TxlGroupEntity();
            txlGroupEntity.txlgroup_id = int.Parse(result[0][0]);
            txlGroupEntity.txlgroup_name = result[0][1];
            txlGroupEntity.txlgroup_descn = result[0][2];
            txlGroupEntity.user_id = int.Parse(result[0][3]);

            return txlGroupEntity;
        }

        public TxlGroupEntity QueryEntity(string user_id, string txlgroup_name)
        {
            TxlGroupEntity txlGroupEntity = null;
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@txlgroup_name", txlgroup_name));
            parameterList.Add(new SqlParameter("@user_id", user_id));
            string sql = "select txlgroup_id,txlgroup_name,txlgroup_descn,user_id " +
                " from txl_group where txlgroup_name=@txlgroup_name and user_id=@user_id";

            var result = DBExecuteUtil.querySqlArray(sql, parameterList.ToArray());
            if (result == null) return null;

            txlGroupEntity = new TxlGroupEntity();
            txlGroupEntity.txlgroup_id = int.Parse(result[0][0]);
            txlGroupEntity.txlgroup_name = result[0][1];
            txlGroupEntity.txlgroup_descn = result[0][2];
            txlGroupEntity.user_id = int.Parse(result[0][3]);

            return txlGroupEntity;
        }

        public DataTable QueryEntityByUserId(int user_id)
        {
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@user_id", user_id));
            string sql = "select txlgroup_id 小组编号,txlgroup_name 名称,txlgroup_descn 备注,user_id 用户编号 " +
                " from txl_group where user_id=@user_id";

            var dt = DBExecuteUtil.querySqlTable(sql, parameterList.ToArray());
            return dt;

        }

        public DataTable QueryByUserId(int user_id)
        {
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@user_id", user_id));
            string sql = "select txlgroup_id 小组编号,txlgroup_name 名称,txlgroup_descn 备注,user_id 用户编号 " +
                " from txl_group where user_id=@user_id";

            var dt = DBExecuteUtil.querySqlTable(sql, parameterList.ToArray());
            return dt;
        }
    }
}
